﻿Imports System.Data.OleDb
Imports System.Data.Odbc

Public Class DbfReadHelper

    ''' <summary>
    ''' 读取DBF文件的方式
    ''' </summary>
    ''' <remarks></remarks>
    Public Class ConnType

        ''' <summary>
        ''' Microsoft dBASE Driver (*.dbf)
        ''' </summary>
        ''' <remarks></remarks>
        Public Const MS_DBASE_DRIVER As String = "MS dBASE Driver"

        ''' <summary>
        ''' Microsoft Visual FoxPro Driver
        ''' </summary>
        ''' <remarks></remarks>
        Public Const MS_VFP_DRIVER As String = "MS VFP Driver"

        ''' <summary>
        ''' vfpoledb
        ''' </summary>
        ''' <remarks></remarks>
        Public Const MS_VFP_OLEDB As String = "MS VFP Oledb"

    End Class

    ''' <summary>
    ''' 从DBF文件中读取数据表（DataTable）
    ''' </summary>
    ''' <param name="filePath">文件路径</param>
    ''' <param name="connType">读取方式</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetDataTable(filePath As String, sql As String, connType As String) As DataTable

        Dim dtResult As DataTable = New DataTable

        Select Case connType

            Case DbfReadHelper.ConnType.MS_DBASE_DRIVER
                Return GetDataTableDBaseDriver(filePath, sql)
            Case DbfReadHelper.ConnType.MS_VFP_DRIVER
                Return GetDataTableVfpDriver(filePath, sql)
            Case DbfReadHelper.ConnType.MS_VFP_OLEDB
                Return GetDataTableVfpOledb(filePath, sql)

        End Select

        Return dtResult

    End Function

    ''' <summary>
    ''' Microsoft dBASE Driver (*.dbf)
    ''' </summary>
    ''' <param name="filePath">读取的文件路径</param>
    ''' <returns>数据表</returns>
    ''' <remarks></remarks>
    Private Shared Function GetDataTableDBaseDriver(filePath As String, sql As String) As DataTable

        Dim strConnString As String = ""
        strConnString &= "Driver={Microsoft dBASE Driver (*.dbf)}; "
        strConnString &= "SourceType=DBF; "
        strConnString &= "Data Source=" & filePath & "; "
        strConnString &= "Exclusive=No; "
        strConnString &= "NULL=NO; "
        strConnString &= "Collating=Machine; "
        strConnString &= "BACKGROUNDFETCH=NO; "
        strConnString &= "DELETE=NO; "

        Dim odbcConnection As OdbcConnection = New OdbcConnection

        odbcConnection.ConnectionString = strConnString
        odbcConnection.Open()

        Dim odbcDataAdapter As OdbcDataAdapter = New OdbcDataAdapter(sql, odbcConnection)
        Dim dtTemp As DataTable = New DataTable
        odbcDataAdapter.Fill(dtTemp)
        odbcConnection.Close()

        Return dtTemp

    End Function

    ''' <summary>
    ''' Microsoft Visual FoxPro Driver
    ''' </summary>
    ''' <param name="filePath">读取的文件路径</param>
    ''' <returns>数据表</returns>
    ''' <remarks></remarks>
    Private Shared Function GetDataTableVfpDriver(filePath As String, sql As String) As DataTable

        Dim strConnString As String = ""
        strConnString &= "Driver={Microsoft Visual FoxPro Driver}; "
        strConnString &= "SourceType=DBF; "
        strConnString &= "sourcedb=" & filePath & "; "
        strConnString &= "BACKGROUNDFETCH=NO; "
        strConnString &= "DELETE=NO; "

        Dim odbcConnection As OdbcConnection = New OdbcConnection

        odbcConnection.ConnectionString = strConnString
        odbcConnection.Open()

        Dim odbcDataAdapter As OdbcDataAdapter = New OdbcDataAdapter(sql, odbcConnection)
        Dim dtTemp As DataTable = New DataTable
        odbcDataAdapter.Fill(dtTemp)
        odbcConnection.Close()

        Return dtTemp

    End Function

    ''' <summary>
    ''' vfpoledb
    ''' </summary>
    ''' <param name="filePath">读取的文件路径</param>
    ''' <returns>数据表</returns>
    ''' <remarks></remarks>
    Private Shared Function GetDataTableVfpOledb(filePath As String, sql As String) As DataTable

        Dim strConnString As String = ""
        strConnString &= "Provider=vfpoledb; "
        strConnString &= "Data Source=" & filePath & "; "
        strConnString &= "Collating Sequence=machine; "

        Using oledbConnnection As OleDbConnection = New OleDbConnection(strConnString)

            Dim oleDbDataAdapter As OleDbDataAdapter = New OleDbDataAdapter(sql, oledbConnnection)
            Dim dtTemp As DataTable = New DataTable
            oleDbDataAdapter.Fill(dtTemp)
            oledbConnnection.Close()

            Return dtTemp

        End Using

        Return Nothing

    End Function

End Class
